package models;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;

public class CustomerPurchaseModel extends AbstractModel {
	
	private int receiptId = -1;
	static private String cid = "";
	private String card_no;
	private Date expire_date;

	public boolean isQuantityEnough(String sname, int upc, int stock)
	{
		try {
			ps = con.prepareStatement("SELECT stock FROM Stored WHERE sname = ? AND upc = ? ");
			ps.setString(1, sname);
			ps.setInt(2, upc);
			ResultSet rs = ps.executeQuery();
			
			if (rs.next())
				if (rs.getInt("stock") >= stock) return true;
			return false;
		} catch (SQLException ex) {
			ex.printStackTrace();
			return registerException(ex);
		}
	}
	public int generateReceipt(String sname)
	{
		try {
			ps = con.prepareStatement("INSERT INTO Purchase VALUES (receiptId_counter.nextval, sysdate, ?,?,?,?,?,?)");
			ps.setString(1, cid);
			ps.setString(2, sname);
			ps.setString(3,card_no);
			
			ps.setDate(4, expire_date);
			
			//Check to see if this purchase is online, and set the expected date accordingly  
			if (cid != null) {
				PreparedStatement ps2 = con.prepareStatement
				("SELECT COUNT(receiptid) FROM Purchase WHERE sname = 'Warehouse' " +
						"AND TRUNC(expecteddate) IN TRUNC((SELECT MAX(expecteddate) FROM Purchase))");
				
				ResultSet rs = ps2.executeQuery();
				rs.next();
				int num_of_deliveries = rs.getInt(1);
				Date expectedDate;

				//If no items have been ordered, set date to tomorrow
				if (num_of_deliveries == 0) {
					expectedDate = new Date(Calendar.getInstance().getTime().getTime());
					expectedDate.setDate(expectedDate.getDate()+1);
				}
				else {
					ps2 = con.prepareStatement("SELECT MAX(expecteddate) FROM Purchase");
					rs = ps2.executeQuery();
					rs.next();
					expectedDate = rs.getDate(1);
					//If number of items to be delivered on the latest date is more than 10,
					//set expected date to next day
					if (num_of_deliveries > 10) expectedDate.setDate(expectedDate.getDate()+1);
				}
				ps.setDate(5, expectedDate);
			}
			else ps.setDate(5, null);
			
			ps.setDate(6, null);
			ps.executeUpdate();
			ps = con.prepareStatement("SELECT receiptId_counter.currval FROM dual");
			ResultSet rs = ps.executeQuery();
			rs.next();
			return rs.getInt(1);
		} catch(SQLException ex) { ex.printStackTrace(); return -1; }
	}
	
	public boolean processPurchase(String sname, int upc, int quantity)
	{
		if (receiptId == -1) receiptId = generateReceipt(sname);
		try {
			ps = con.prepareStatement("SELECT stock FROM Stored WHERE sname = ? AND upc = ? ");
			ps.setString(1, sname);
			ps.setInt(2, upc);
			ResultSet rs = ps.executeQuery();
			rs.next();
			int newStock = rs.getInt("stock") - quantity;
			
			ps = con.prepareStatement("UPDATE Stored SET stock = ? WHERE sname = ? AND upc = ? ");
			ps.setInt(1, newStock);
			ps.setString(2, sname);
			ps.setInt(3, upc);
			ps.executeUpdate();
			
			ps = con.prepareStatement("INSERT INTO PurchaseItem VALUES (?,?,?)");
			ps.setInt(1, receiptId);
			ps.setInt(2, upc);
			ps.setInt(3, quantity);
			ps.executeUpdate();
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			try { con.rollback(); }
			catch (SQLException ex1) { ex1.printStackTrace(); registerException(ex1); }
			return registerException(ex);
		}
		return true;
	}
	
	public ResultSet printReceipt() {
		try {
			ps = con.prepareStatement("SELECT Item.title, Item.sellprice, PurchaseItem.quantity, " +
					"Item.sellprice*PurchaseItem.quantity \"ITEM SUBTOTAL\" FROM Item, PurchaseItem " +
					"WHERE Item.upc = PurchaseItem.upc AND PurchaseItem.receiptId = " + receiptId);
			
			return ps.executeQuery();
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			registerException(ex);
			return null;
		}
	}

	public int commit()
	{
		try {
			con.commit();
			int tmp_receiptId = receiptId; 
			receiptId = -1;
			return tmp_receiptId;
			
		} catch (SQLException ex) {
			ex.printStackTrace();
			registerException(ex);
			return -1;
		}
	}
	
	public void setCardInfo(String card_no, Date expire_date) {
		this.card_no = card_no;
		this.expire_date = expire_date;
	}
	
	public static void setCID(String customer_id) { cid = customer_id; }
	public static String getCID() { return cid; }
	
	@Override
	public ResultSet show() {
		// TODO Auto-generated method stub
		return null;
	}
}
